﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;

namespace CanSWAT.SWAT.Output
{
    /// <summary>
    /// Output of one reach, subbasin, hru or reservoir
    /// </summary>
    class SWATOutputForOneElement
    {
        public SWATOutputForOneElement(int id, SWATOutputType type, 
            SWATOutputIntervalType interval, DataTable allData)
        {
            _elementID = id;
            _elementType = type;
            _intervalType = interval;
            _allData = allData;
        }

        private int _elementID;
        private SWATOutputType _elementType;
        private SWATOutputIntervalType _intervalType;
        private DataTable _allData = null;

        public static Dictionary<int, SWATOutputForOneElement> FromAllData(SWATOutputType type,DataTable dt)
        {
            Dictionary<int, SWATOutputForOneElement> elements = new Dictionary<int, SWATOutputForOneElement>();

            SWATOutputIntervalType interval = DatabaseUtil.GetInterval(dt);
            if (interval == SWATOutputIntervalType.UNKNOWN) return elements;

            string idCol = SWATOutputDatabase.IDColNameFromType(type);
            List<int> ids = DatabaseUtil.GetUniqueIDs(dt, idCol);
            foreach (int id in ids)
            {
                DataRow[] rows = dt.Select(idCol + "=" + id.ToString());
                DataTable data = dt.Clone();
                foreach (DataRow r in rows)
                    data.ImportRow(r);

                elements.Add(id,new SWATOutputForOneElement(id, type, interval, data));
            }
            return elements;
        }

        #region Statistics, yearly total/average, monthly total/average
        


        #endregion

        #region Plot

        #region Add Date Column

        private void calculateDate(DataRow r)
        {
            DateTime d = DateTime.Now;

            int year = Convert.ToInt32(DatabaseUtil.ReadValue(r,SWATOutputDatabase.COLUMN_NAME_YEAR));
            if (_intervalType == SWATOutputIntervalType.YEARLY)
                d = new DateTime(year, 1, 1);
            else if (_intervalType == SWATOutputIntervalType.MONTHLY)
                d = new DateTime(year, Convert.ToInt32(DatabaseUtil.ReadValue(r,SWATOutputDatabase.COLUMN_NAME_MON)), 1);
            else if (_intervalType == SWATOutputIntervalType.DAILY)
            {
                d = new DateTime(year, 1, 1);
                d = d.AddDays(DatabaseUtil.ReadValue(r,SWATOutputDatabase.COLUMN_NAME_MON) - 1);
            }

            r[SWATOutputDatabase.COLUMN_NAME_DATE] = d;
        }

        /// <summary>
        /// Add date column, only needed when plot
        /// </summary>
        private void addDateColumn()
        {
            if (_allData == null) return;
            if (_allData.Columns.Contains(SWATOutputDatabase.COLUMN_NAME_DATE)) return;

            try
            {
                //add column
                _allData.Columns.Add(SWATOutputDatabase.COLUMN_NAME_DATE, typeof(DateTime));


                //get date
                foreach (DataRow r in _allData.Rows)
                    calculateDate(r);
            }
            catch (System.Exception e)
            {
                Debug.WriteLine(e.Message);
            }
        }

        #endregion

        /// <summary>
        /// plot data of given year
        /// </summary>
        /// <param name="year"></param>
        /// <param name="colName"></param>
        /// <returns></returns>
        public DataRow[] Plot(int year, string colName)
        {
            if (year > 0)
                return Plot(SWATOutputDatabase.COLUMN_NAME_YEAR + "=" + year.ToString(), colName);
            else
                return Plot(colName);
        }

        /// <summary>
        /// plot all data
        /// </summary>
        /// <param name="colName"></param>
        /// <returns></returns>
        public DataRow[] Plot(string colName)
        {
            return Plot("", colName);
        }

        private DataRow[] Plot(string filter, string colName)
        {
            addDateColumn();
            if(_allData == null || !_allData.Columns.Contains(SWATOutputDatabase.COLUMN_NAME_DATE))
                return null;

            return search(filter);
        }

        private Dictionary<string, DataRow[]> _searchResults =
            new Dictionary<string, DataRow[]>();

        private DataRow[] search(string filter)
        {
            try
            {
                if(_allData == null) return new DataRow[]{};
                if (!_searchResults.ContainsKey(filter))
                    _searchResults.Add(filter, _allData.Select(filter));
                return _searchResults[filter];
            }
            catch
            {
                Debug.WriteLine("Please check the filter:" + filter);
                return new DataRow[]{};
            }
        }

        //private DataSourcePointList getPointsFromRows(DataRow[] rows, string colName)
        //{
        //    if (rows == null || rows.Length == 0) return null;
        //    if (!rows[0].Table.Columns.Contains(colName))
        //    {
        //        Debug.WriteLine("Could't find column " + colName);
        //        return null;
        //    }
        //    if (!rows[0].Table.Columns.Contains(SWATOutputDatabase.COLUMN_NAME_DATE))
        //    {
        //        Debug.WriteLine("Could't find column " + SWATOutputDatabase.COLUMN_NAME_DATE);
        //        return null;
        //    }
            
        //    DataSourcePointList points = new DataSourcePointList();
        //    points.DataSource = rows;
        //    points.XDataMember = SWATOutputDatabase.COLUMN_NAME_DATE; //date column
        //    points.YDataMember = colName;   //value column
        //    return points;
        //}

        #endregion
    }

    /// <summary>
    /// Corresponding to one table in output database
    /// </summary>
    class SWATOutputForOneType
    {
        public SWATOutputForOneType(SWATOutputType type, DataTable dt)
        {
            _elementType = type;
            _data = dt;
        }

        private SWATOutputType _elementType;
        private DataTable _data = null;

        #region Plot for ZedGraph

        public DataRow[] Plot(int id, int year, string valueColName)
        {
            SWATOutputForOneElement ele = this.getElement(id);
            return ele.Plot(year,valueColName);
        }

        #endregion

        #region Elements

        private Dictionary<int, SWATOutputForOneElement> _elements =
            new Dictionary<int, SWATOutputForOneElement>();

        private SWATOutputForOneElement getElement(int id)
        {
            if (!_elements.ContainsKey(id)) 
            {
                string idColName = SWATOutputDatabase.IDColNameFromType(_elementType);
                string sql = idColName + "=" + id.ToString();
                DataRow[] rows = _data.Select(sql);
                DataTable data = _data.Clone();
                foreach (DataRow r in rows)
                    data.ImportRow(r);
                _elements.Add(
                    id,
                    new SWATOutputForOneElement(id, _elementType, TimeInterval, data));                        
            }
            return _elements[id];
        }

        #endregion

        #region IDs

        private List<int> _ids = null;

        public List<int> IDs
        {
            get
            {
                if (_ids == null) 
                    _ids = DatabaseUtil.GetUniqueIDs(_data, SWATOutputDatabase.IDColNameFromType(_elementType));
                return _ids;
            }
        }

        #endregion

        #region Years

        private List<int> _years = null;

        public List<int> Years
        {
            get
            {
                if (_years == null)
                {
                    _years = DatabaseUtil.GetUniqueIDs(_data, SWATOutputDatabase.COLUMN_NAME_YEAR);
                    _years.Insert(0, 0);
                }
                return _years;
            }
        }

        #endregion

        #region Time Interval

        private SWATOutputIntervalType _intervalType = SWATOutputIntervalType.UNKNOWN;

        public SWATOutputIntervalType TimeInterval
        {
            get
            {
                if (_intervalType == SWATOutputIntervalType.UNKNOWN && _data != null
                    && _data.Columns.Contains(SWATOutputDatabase.COLUMN_NAME_MON))
                {
                    try
                    {
                        DataView view = new DataView(_data);
                        DataTable dt = view.ToTable(true, SWATOutputDatabase.COLUMN_NAME_MON);
                        if (dt.Rows.Count == 12) _intervalType = SWATOutputIntervalType.MONTHLY;
                        else if (dt.Rows.Count <= 366) _intervalType = SWATOutputIntervalType.DAILY;
                        else _intervalType = SWATOutputIntervalType.YEARLY;
                    }
                    catch (System.Exception e)
                    {
                        Debug.WriteLine(e.Message);
                    }
                }
                return _intervalType;
            }
        }

        #endregion

        #region Columns

        private StringCollection _cols = null;

        public StringCollection Columns
        {
            get
            {
                if (_cols == null) _cols = GetDataColumns(_data);
                return _cols;
            }
        }

        private static StringCollection GetDataColumns(DataTable dt)
        {
            StringCollection cols = new StringCollection();
            if (dt == null || !dt.Columns.Contains(SWATOutputDatabase.COLUMN_NAME_MON)) return cols;

            int startIndex = dt.Columns.IndexOf(SWATOutputDatabase.COLUMN_NAME_MON) + 1;
            for (int i = startIndex; i < dt.Columns.Count; i++)
                cols.Add(dt.Columns[i].ColumnName);
            return cols;
        }

        #endregion
    }

    /// <summary>
    /// SWATOutput.mdb generated using ArcSWAT
    /// </summary>
    class SWATOutputDatabase : SWATFile
    {
        public SWATOutputDatabase(string file):base(file)
        {
        }

        private static string[] TABLE_NAMES = {"rch","sub","hru","rsv","sed"};
        private static string[] ID_COLUMN_NAMES = { "SUB", "SUB", "hru", "rsv", "sed" };

        public static string COLUMN_NAME_MON = "MON";
        public static string COLUMN_NAME_SUB = "SUB";
        public static string COLUMN_NAME_YEAR = "YEAR";
        public static string COLUMN_NAME_DATE = "DATE1";

        #region All Tables

        private StringCollection _tables = null;

        public StringCollection Tables
        {
            get
            {
                if (_tables == null)
                {
                    _tables = new StringCollection();
                    using (OleDbConnection con = new OleDbConnection(ConnectString))
                    {
                        con.Open();
                        DataTable schemaTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        foreach (DataRow r in schemaTable.Rows)
                        {
                            string tableName = r["TABLE_NAME"].ToString().Trim();
                            if(tableName.Length == 3)
                                _tables.Add(tableName);
                        }
                    }                    
                }
                return _tables;
            }
        }

        #endregion

        #region Plot for ZedGraph

        public DataRow[] Plot(SWATOutputType type, int id, int year, string valueColName)
        {
            SWATOutputForOneType output = this.getOutput(type);
            return output.Plot(id, year, valueColName);
        }

        #endregion

        #region Outputs

        private Dictionary<SWATOutputType, SWATOutputForOneType> _outputs =
            new Dictionary<SWATOutputType, SWATOutputForOneType>();

        public SWATOutputForOneType getOutput(string outputTableName)
        {
            return getOutput(TypeFromTableName(outputTableName));
        }

        public SWATOutputForOneType getOutput(SWATOutputType type)
        {
            Debug.WriteLine("Get data for " + type.ToString());

            if (type == SWATOutputType.UNKNOWN) return null;

            if (!_outputs.ContainsKey(type))
                _outputs.Add(type, new SWATOutputForOneType(type, GetTable(type)));

            return _outputs[type];
        }

        #endregion

        #region Data Table

        private string ConnectString
        {
            get
            {
                return string.Format(
                    "Provider=microsoft.Jet.oledb.4.0;Data Source={0};",File);
            }
        }

        private static SWATOutputType TypeFromTableName(string outputTableName)
        {
            int index = System.Array.IndexOf(TABLE_NAMES,outputTableName.ToLower());
            return (SWATOutputType)index;
        }

        public static string TableNameFromType(SWATOutputType type)
        {
            return TABLE_NAMES[Convert.ToInt32(type)];
        }

        public static string IDColNameFromType(SWATOutputType type)
        {
            return ID_COLUMN_NAMES[Convert.ToInt32(type)];
        }

        private DataTable GetTable(SWATOutputType type)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter(
                "select * from " + TableNameFromType(type),
                ConnectString);
            DataTable dt = new DataTable();
            try
            {                
                adapter.Fill(dt);
            }
            catch (System.Exception e)
            {
                Debug.WriteLine(e.Message);
            }
            return dt;
        }

        #endregion        

    }

    class DatabaseUtil
    {
        public static SWATOutputIntervalType GetInterval(DataTable dt)
        {
            if (dt == null || !dt.Columns.Contains(SWATOutputDatabase.COLUMN_NAME_MON)) 
                return SWATOutputIntervalType.UNKNOWN;

            try
            {
                DataView view = new DataView(dt);
                DataTable dtt = view.ToTable(true, SWATOutputDatabase.COLUMN_NAME_MON);
                if (dtt.Rows.Count == 12) return SWATOutputIntervalType.MONTHLY;
                else if (dtt.Rows.Count <= 366) return SWATOutputIntervalType.DAILY;
                else return SWATOutputIntervalType.YEARLY;
            }
            catch (System.Exception e)
            {
                Debug.WriteLine(e.Message);
                return SWATOutputIntervalType.UNKNOWN;
            }
        }

        public static List<int> GetUniqueIDs(DataTable dt, string idColName)
        {
            List<int> ids = new List<int>();
            if (dt == null || !dt.Columns.Contains(idColName)) return ids;

            DataView view = new DataView(dt);
            DataTable dt2 = view.ToTable(true, idColName);
            foreach (DataRow r in dt2.Rows)
                ids.Add(Convert.ToInt32(ReadValue(r[0])));
            return ids;
        }

        public static double ReadValue(DataRow r, string colName)
        {
            if(!r.Table.Columns.Contains(colName))
            {
                Debug.WriteLine("Coundn't find column " + colName);
                return 0.0;
            }
            return ReadValue(r[colName]);
        }

        public static double ReadValue(object v)
        {
            double value = 0.0;
            if (System.DBNull.Value.Equals(v)) return value;
            double.TryParse(v.ToString(), out value);
            return value;
        }
    }

    enum SWATOutputType
    {
        UNKNOWN = -1,
        REACH = 0,
        SUBBASIN = 1,
        HRU = 2,
        RESERVOIR = 3,
        SEDIMENT = 4
    }

    enum SWATOutputIntervalType
    {
        DAILY,
        MONTHLY,
        YEARLY,
        UNKNOWN
    }
}
